package com.lisa.util.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.lisa.util.UtilCommon;

public class DBManager {

	/**
	 * 获取链接
	 * 
	 * @return
	 * @throws SQLException
	 */
	private static Connection getConn() throws Exception {
		Connection conn = null; // 创建一个数据库连接
		try {
			// System.out.println("开始尝试连接数据库！");
			Class.forName(UtilCommon.DRIVER); // classLoader,加载对应驱动
			conn = (Connection) DriverManager.getConnection(UtilCommon.URL,
					UtilCommon.USERNAME, UtilCommon.PASSWORD);
			// System.out.println("连接成功！");

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			// System.out.println("-------数据库驱动错误---------------------------\n\r");
			System.out.print(e.getMessage());
		} catch (SQLException e) {
			e.printStackTrace();
			// System.out.println("-------连接失败---------------------------\n\r");
			System.out.print(e.getMessage());
			throw e;
		}
		return conn;
	}

	/**
	 * 构建插入语句
	 * 
	 * @param tableName
	 *            表名称
	 * @param data
	 *            数据键值对， key：字段名、value：值
	 * @return
	 */
	private static String constructInsertString(String tableName,
			Map<String, Object> data) {
		String strKeys = "";
		String strValues = "";
		Iterator<String> keys = data.keySet().iterator();
		while (keys.hasNext()) {
			String strKeyCurrent = keys.next();
			strKeys += "," + strKeyCurrent;
			String strValueCurrent = String.valueOf(data.get(strKeyCurrent));
			if (strValueCurrent == null) {
				strValueCurrent = "";
			}
			strValues += ",'" + strValueCurrent + "' ";
		}
		strKeys = strKeys.substring(1);
		strValues = strValues.substring(1);
		String sql = "insert into " + tableName + " (" + strKeys + ") values("
				+ strValues + ")";
		return sql;
	}

	/**
	 * 根据字段名组织SQL插入语句
	 * 
	 * @param tableName
	 * @param keys
	 * @return
	 */
	private static String constructInsertString(String tableName,
			List<String> keys) {
		String strKeys = "";
		String strValues = "";
		for (String key : keys) {
			strKeys += "," + key;
			strValues += ",?";
		}
		strKeys = strKeys.substring(1);
		strValues = strValues.substring(1);
		String sql = "insert into " + tableName + " (" + strKeys + ") values("
				+ strValues + ")";
		return sql;
	}

	/**
	 * 插入多条输入
	 * 
	 * @param tableName
	 * @param records
	 * @return
	 * @throws Exception
	 */
	public static int insert(String tableName, List<Map<String, Object>> records)
			throws Exception {
		int count = records.size();
		// 获取列名称
		// List<String> sqlList = new ArrayList<String>();
		Map<String, Object> record = records.get(0); // 找到要插入的集合中的第一条数据
		Iterator<String> iterkeys = record.keySet().iterator(); // 遍历一条完整数据获得他们的key值（与数据库字段名对应）
		List<String> strKeys = new ArrayList<String>(); // 列名的集合
		while (iterkeys.hasNext()) {
			strKeys.add(iterkeys.next());
		}
		// 获取sql
		String sql = constructInsertString(tableName, strKeys);

		Map<String, Integer> cols = getColumn(tableName); // 获得数据库中数据的字段名

		Connection conn = getConn();
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			// final int batchSize = 1000;
			for (Map<String, Object> recordC : records) {
				Object value = null;
				for (int i = 0; i < strKeys.size(); i++) {
					value = recordC.get(strKeys.get(i));
					if (value == null) {
						pstmt.setObject(i + 1, null);
						continue;
					}
					switch (cols.get(strKeys.get(i))) {
					case java.sql.Types.DATE:
						pstmt.setTimestamp(i + 1,
								Timestamp.valueOf((String) value));
						break;
					case java.sql.Types.TIME:
					case java.sql.Types.TIMESTAMP:
						pstmt.setTimestamp(i + 1,
								Timestamp.valueOf((String) value));
						break;
					default:
						pstmt.setObject(i + 1, recordC.get(strKeys.get(i)));
						break;
					}
				}
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			conn.commit();
			pstmt.close();
			conn.close();
			// System.out.println("-------插入成功---------------------------");
		} catch (SQLException e) {
			e.printStackTrace();
			// System.out.println("-------插入失败---------------------------\n\r");
			System.out.print(e.getMessage());
			throw e;
		} finally {
			try {
				// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
				// 注意关闭的顺序，最后使用的最先关闭
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
				// System.out.println("数据库连接已关闭！");
			} catch (Exception e) {
				e.printStackTrace();
				// System.out.println("-------数据库关闭失败---------------------------\n\r");
				System.out.print(e.getMessage());
				throw e;
			}
		}

		return count;
	}

	/**
	 * 插入数据
	 * 
	 * @param tableName
	 *            表名称
	 * @param data
	 *            数据键值对， key：字段名、value：值
	 * @return
	 * @throws Exception
	 */
	public static int insert(String tableName, Map<String, Object> data)
			throws Exception {
		int i = 0;
		String sql = constructInsertString(tableName, data);
		PreparedStatement pstmt = null;
		Connection conn = getConn();
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			i = pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
				// 注意关闭的顺序，最后使用的最先关闭
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();

				// System.out.println("数据库连接已关闭！");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return i;
	}

	/**
	 * 获取所有记录
	 * 
	 * @param tableName
	 *            表名称
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> getAllRecords(String tableName)
			throws Exception {
		//
		Iterator<String> colomns = getColumn(tableName).keySet().iterator();
		List<String> strColomn = new ArrayList<String>();
		while (colomns.hasNext()) {
			strColomn.add(colomns.next());
		}
		String[] strColomnArray = strColomn
				.toArray(new String[strColomn.size()]);
		List<Map<String, Object>> result = getRecords(tableName, null, null,
				strColomnArray);
		return result;
	}

	/**
	 * 查询制定条件记录的指定字段
	 * 
	 * @param tableName
	 *            表名称
	 * @param strFields
	 *            字段列表: 若为null，则提取所有列
	 * @param strfilter
	 *            筛选条件 若为null，则提取全表
	 * @param fields
	 *            字段集合
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> getRecords(String tableName,
			String strFields, String strfilter, String[] fields)
			throws Exception {
		// 查询结果
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();

		// 构建SQL语句
		String sql = "select ";
		sql += (strFields == null ? "*" : strFields);
		sql += " from " + tableName;
		sql += (strfilter == null ? "" : " where " + strfilter);

		Connection conn = getConn(); // 获取连接
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < fields.length; i++) {
					String value = rs.getString(fields[i]);
					map.put(fields[i], value);
				}
				result.add(map);
			}
			// System.out.println("---------查询成功！-------------");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
				// 注意关闭的顺序，最后使用的最先关闭
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
				// System.out.println("数据库连接已关闭！");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * 查询全部记录的指定字段
	 * 
	 * @param tableName
	 *            表名称
	 * @param fields
	 *            字段集合
	 * @param filter
	 *            筛选条件键值对 key： 列名称 value： 列值
	 * @param connection
	 *            筛选条件连接符
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> getRecords(String tableName,
			String[] fields, Map<String, String> filter, String connection)
			throws Exception {
		// 构建查询字段
		String strFields = "";
		for (String str : fields) {
			strFields += "," + str;
		}
		strFields = strFields.substring(1);
		// 构建查询条件
		String strFilter = constructFilter(filter, connection);
		// 查询结果
		List<Map<String, Object>> result = getRecords(tableName, strFields,
				strFilter, fields);
		return result;
	}

	/**
	 * 查询全部记录的指定字段
	 * 
	 * @param tableName
	 *            表名称
	 * @param fields
	 *            字段集合
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> getAllRecords(String tableName,
			String[] fields) throws Exception {
		// 构建查询字段
		String strFields = "";
		for (String str : fields) {
			strFields += "," + str;
		}
		strFields = strFields.substring(1);
		// 查询结果
		List<Map<String, Object>> result = getRecords(tableName, strFields,
				null, fields);
		return result;
	}

	/**
	 * 查找表的列
	 * 
	 * @param tableName
	 *            表名
	 * @return 表的列， Key： 列名； Value: 数据类型
	 * @throws Exception
	 */
	public static Map<String, Integer> getColumn(String tableName)
			throws Exception {
		Map<String, Integer> result = new HashMap<String, Integer>();

		Connection conn = getConn();
		String sql = "select * from " + tableName;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int col = rs.getMetaData().getColumnCount();
			for (int i = 1; i <= col; i++) {
				result.put(rs.getMetaData().getColumnName(i), rs.getMetaData()
						.getColumnType(i));
			}
			// UtilPrint.printColomnNames(result);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
				// 注意关闭的顺序，最后使用的最先关闭
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();

				// System.out.println("数据库连接已关闭！");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * 根据数据库查询结果，获取其列名称
	 * 
	 * @param resultAll
	 *            从数据库中查询得到的数据
	 * @return 列名称
	 */
	public static String[] getColumnNames(List<Map<String, Object>> resultAll) {
		if (resultAll != null && resultAll.size() > 0) {
			Iterator<String> keys = resultAll.get(0).keySet().iterator();
			List<String> fields = new ArrayList<String>();
			while (keys.hasNext()) {
				fields.add(keys.next());
			}

			String[] results = fields.toArray(new String[fields.size()]);
			// UtilPrint.printColomnNames(results);
			return results;
		} else {
			return null;
		}
	}

	/**
	 * 获取表的所有列名
	 * 
	 * @param rs
	 *            查询结果
	 * @return 字段名
	 * @throws SQLException
	 */
	public static String[] getColumnNames(ResultSet rs) throws SQLException {
		int col = rs.getMetaData().getColumnCount();
		String[] results = new String[col];
		String colName = "";
		for (int i = 1; i <= col; i++) {
			colName = rs.getMetaData().getColumnName(i);
			results[i - 1] = colName;
		}
		// UtilPrint.printColomnNames(result);
		return results;
	}

	/**
	 * 获取所有的表
	 * 
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> getAllTableName() throws Exception {
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		Connection conn = getConn();
		String sql = "select table_name from user_tables";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			// System.out.println("============================");
			String[] cols = getColumnNames(rs);
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < cols.length; i++) {
					String value = rs.getString(cols[i]);
					map.put(cols[i], value);

					System.out.print(rs.getString(cols[i]) + "\t");
					if ((i == 2) && (rs.getString(cols[i]).length() < 8)) {
						System.out.print("\t");
					}
				}
				result.add(map);
				System.out.println("");
			}
			// System.out.println("============================");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
				// 注意关闭的顺序，最后使用的最先关闭
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
				// System.out.println("数据库连接已关闭！");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * 筛选条件
	 * 
	 * @param filter
	 *            筛选条件键值对 key： 列名称 value： 列值
	 * @param connection
	 *            连接符
	 * @return
	 */
	public static String constructFilter(Map<String, String> filter,
			String connection) {
		String strWheres = "";
		if (filter != null) {
			Iterator<String> keys = filter.keySet().iterator();
			while (keys.hasNext()) {
				String strKeyCurrent = keys.next();
				String strValueCurrent = filter.get(strKeyCurrent);
				String strWhere = "";
				if (strValueCurrent != null
						&& !strValueCurrent.equalsIgnoreCase("")) {
					strWhere = strKeyCurrent + " = " + strValueCurrent;
				}
				strWheres += " " + connection + " " + strWhere;
			}
			strWheres = strWheres.substring(1).replaceFirst(connection, " ");
		}
		return strWheres;
	}

	/**
	 * 查找记录
	 * 
	 * @param tableName
	 *            表名称
	 * @param filter
	 *            判断条件键值对： key：字段名，value：值
	 * @param colomns
	 *            输出字段
	 * @return 查询的数据结果
	 * @throws Exception
	 */
	public static List<Map<String, Object>> query(String tableName,
			HashMap<String, String> filter, String[] colomns) throws Exception {
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();

		String strWheres = constructFilter(filter, "&&");
		String strFields = "";
		for (String str : colomns) {
			strFields += "," + str;
		}
		strFields = strFields.substring(1);

		String sql = "select " + strFields + " from " + tableName + " where "
				+ strWheres;
		Connection conn = getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < colomns.length; i++) {
					String value = rs.getString(colomns[i]);
					map.put(colomns[i], value);

					/*
					 * System.out.print(rs.getString(fields[i]) + "\t"); if ((i
					 * == 2) && (rs.getString(fields[i]).length() < 8)) {
					 * System.out.print("\t"); }
					 */
				}
				result.add(map);

				/* System.out.println(""); */
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
				// 注意关闭的顺序，最后使用的最先关闭
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
				// System.out.println("数据库连接已关闭！");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * 删除记录
	 * 
	 * @param tableName
	 *            表名称
	 * @param data
	 *            判断条件键值对： key：字段名，value：值
	 * @return 删除记录的条数
	 * @throws Exception
	 */
	public static int delete(String tableName, Map<String, String> filter)
			throws Exception {
		int i = 0;
		String strWheres = constructFilter(filter, "AND");
		String sql = "delete from " + tableName + " where " + strWheres;

		// System.out.println("要删除的目标数据：" + sql);

		Connection conn = getConn();
		PreparedStatement pstmt = null;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			i = pstmt.executeUpdate();
			// System.out.println("resutl: " + i);
			// System.out.println("-------删除成功 删除数量"+String.valueOf(i)+"---------------------------");
		} catch (SQLException e) {
			e.printStackTrace();
			// System.out.println("-------删除失败---------------------------\n\r");
			System.out.print(e.getMessage());
			throw e;
		} finally {
			try {
				// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
				// 注意关闭的顺序，最后使用的最先关闭
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
				// System.out.println("数据库连接已关闭！");
			} catch (Exception e) {
				e.printStackTrace();
				// System.out.println("-------关闭失败---------------------------\n\r");
				System.out.print(e.getMessage());
				throw e;
			}
		}
		return i;
	}

	/**
	 * 查询表中是否存在某一字段
	 * 
	 * @param tableName
	 *            目标数据表
	 * @param countName
	 *            查询的目标字段
	 * @return
	 * @throws Exception
	 */
	public static boolean selectCount(String tableName, String countName)
			throws Exception {
		boolean hasColumn = false;
		String sql = "select * from USER_TAB_COLUMNS t where t.TABLE_NAME = '"
				+ tableName + "' and t.COLUMN_NAME = '" + countName + "'";

		Connection conn = getConn();
		PreparedStatement pstmt = null;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			ResultSet resultSet = pstmt.executeQuery();
			if (resultSet.next()) {
				hasColumn = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			hasColumn = false;
			throw e;
		} finally {
			try {
				// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
				// 注意关闭的顺序，最后使用的最先关闭
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
				throw e;
			}
		}
		return hasColumn;
	}
}
